#!/usr/bin/env python
# -*- coding: UTF-8 -*-
from sqlalchemy import asc, func
from .base_dao import getDatabaseSession
from app.dao.models import YmUser


class UserQueryDao(object):
    """用户查询类dao"""

    @classmethod
    def login(cls, student_id: str, password: str) -> YmUser:
        """单条查询示例"""
        with getDatabaseSession() as session:
            query = session.query(YmUser).filter(
                YmUser.student_id == student_id, YmUser.password == password
            )
            result = query.first()

            return result


class UserOperateDao(object):
    """操作用户相关dao"""

    @classmethod
    def saveUser(cls, user: YmUser) -> YmUser:
        """添加单条"""
        with getDatabaseSession(False) as session:
            session.add(user)
            session.commit()
            session.refresh(user)
        return user

    @classmethod
    def saveUserList(cls, users: list[YmUser]):
        """添加单条"""
        with getDatabaseSession() as session:
            session.bulk_save_objects(users)

    @classmethod
    def delete_all(cls):
        with getDatabaseSession() as session:
            session.query(YmUser).delete()
            session.commit()

    @classmethod
    def count_students_by_major(cls) -> list[dict]:
        """
        按专业统计学生数量（去重 student_id）
        """
        with getDatabaseSession() as session:
            result = (
                session.query(
                    YmUser.major,
                    func.count(func.distinct(YmUser.student_id)),
                )
                .group_by(YmUser.major)
                .all()
            )
            return [{"major": row[0], "student_count": row[1]} for row in result]

    @classmethod
    def get_by_major(cls, major: str):
        with getDatabaseSession() as session:
            return (
                session.query(YmUser)
                .filter(YmUser.major.contains(major))
                .order_by(asc(YmUser.student_id))
                .all()
            )
